from datetime import datetime
from typing import List, Tuple, Dict
from fastapi import HTTPException, UploadFile
from tortoise.contrib.pydantic import pydantic_model_creator
from model.sysPostModel import SysPost
from schema.sysPostSchema import SysPostCreate, SysPostUpdate, PageParams, SysPostResponse
from io import BytesIO
from excel.sysPostExcel import SysPostExcel
from tortoise import connections


class PydanticConfig:
    alias_generator = lambda x: ''.join(word.capitalize() if i else word for i, word in enumerate(x.split('_')))
    allow_population_by_field_name = True


SysPost_Pydantic = pydantic_model_creator(SysPost, name="SysPost", config_class=PydanticConfig)


class SysPostService:
    @staticmethod
    async def create(data: SysPostCreate, creator: str) -> SysPost:
        """Create new record"""
        data_dict = data.dict(exclude_unset=True)
        data_dict["create_by"] = creator
        data_dict["create_time"] = datetime.now()
        data_dict["status"] = '0'

        obj = await SysPost.create(**data_dict)
        return await SysPost_Pydantic.from_tortoise_orm(obj)

    @staticmethod
    async def get_by_id(id: int) -> SysPost:
        """Get record by ID"""
        obj = await SysPost.get_or_none(post_id=id, status='0')
        if not obj:
            raise HTTPException(status_code=404, detail="岗位信息表不存在")
        return await SysPost_Pydantic.from_tortoise_orm(obj)

    @staticmethod
    async def update(id: int, data: SysPostUpdate, updater: str) -> SysPost:
        """Update record"""
        obj = await SysPost.get_or_none(post_id=id, status='0')
        if not obj:
            raise HTTPException(status_code=404, detail="岗位信息表不存在")

        update_dict = data.dict(exclude_unset=True)
        update_dict["update_by"] = updater
        update_dict["update_time"] = datetime.now()

        await obj.update_from_dict(update_dict).save()
        return await SysPost_Pydantic.from_tortoise_orm(obj)

    @staticmethod
    async def delete(ids: List[int], updater: str) -> int:
        """Batch delete records"""
        update_data = {
            "status": '2',
            "update_by": updater,
            "update_time": datetime.now()
        }
        count = await SysPost.filter(post_id__in=ids, status='0').update(**update_data)
        return count

    @staticmethod
    async def get_page(params: PageParams) -> Tuple[List[SysPost], int]:
        """Get page list"""
        query = SysPost.filter(status='0')

        # Build query conditions

        if params.post_code:
            query = query.filter(post_code__icontains=params.post_code)

        if params.post_name:
            query = query.filter(post_name__icontains=params.post_name)

        if params.status:
            query = query.filter(status__icontains=params.status)

        if params.use_flag:
            query = query.filter(use_flag__icontains=params.use_flag)

        if params.create_by:
            query = query.filter(create_by__icontains=params.create_by)

        if params.update_by:
            query = query.filter(update_by__icontains=params.update_by)

        if params.remark:
            query = query.filter(remark__icontains=params.remark)

        total = await query.count()

        records = await query.offset((params.page_num - 1) * params.page_size)             .limit(params.page_size)             .order_by("-create_time")

        return [await SysPost_Pydantic.from_tortoise_orm(record) for record in records], total

    @staticmethod
    async def export_excel() -> BytesIO:
        """Export all data to Excel"""
        records = await SysPost.filter(status='0').all()
        return await SysPostExcel.export_data(records)

    @staticmethod
    def get_import_template() -> BytesIO:
        """Get import template"""
        return SysPostExcel.get_import_template()

    @staticmethod
    async def import_data(file: UploadFile, creator: str) -> Tuple[int, List[str]]:
        """Import data"""
        content = await file.read()
        file_bytes = BytesIO(content)

        try:
            data_list = SysPostExcel.parse_import_data(file_bytes)
        except ValueError as e:
            raise HTTPException(status_code=400, detail=str(e))
        except Exception as e:
            raise HTTPException(status_code=400, detail="Excel文件格式错误")

        success_count = 0
        error_msgs = []

        for index, item_data in enumerate(data_list, start=1):
            try:
                item_data['create_by'] = creator
                item_data['create_time'] = datetime.now()
                item_data['status'] = '0'

                await SysPost.create(**item_data)
                success_count += 1

            except Exception as e:
                error_msgs.append(f"第{index}行: {str(e)}")

        return success_count, error_msgs

    @staticmethod
    async def get_all_list():
        data_list = await SysPost.filter(status="0").all()
        return [await SysPost_Pydantic.from_tortoise_orm(record) for record in data_list]

    @staticmethod
    async def get_post_list_by_user(user_id: int) -> List[Dict]:
        """获取用户的岗位列表

        Args:
            user_id (int): 用户ID

        Returns:
            List[Dict]: 岗位信息列表（驼峰式命名）
        """
        query = """
                        SELECT DISTINCT p.*
                        FROM sys_post p
                        INNER JOIN sys_user_post up ON up.post_id = p.post_id
                        WHERE up.user_id = %s
                        AND p.status = '0'  
                        AND p.use_flag = '0'
                        ORDER BY p.post_sort ASC
                    """

        conn = connections.get('default')
        results = await conn.execute_query_dict(query, [user_id])

        # 处理日期时间格式并使用schema转换
        formatted_results = []
        for result in results:
            if result.get('create_time'):
                result['create_time'] = result['create_time'].strftime('%Y-%m-%d %H:%M:%S')
            if result.get('update_time'):
                result['update_time'] = result['update_time'].strftime('%Y-%m-%d %H:%M:%S')

            # 使用 schema 进行转换，确保字段名为驼峰式
            post_model = SysPostResponse(**result)
            formatted_results.append(post_model.dict(by_alias=True))

        return formatted_results
